IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_MediaContentAddMany]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_MediaContentAddMany]
GO
create procedure [dbo].[cms_sp_MediaContentAddMany]
(
@SiteId int = null,
@SiteGroupId int = null,
@MediaId int,
@ContentXml xml,
@CreatedBy int,
@DateCreated datetime
)
as
-- @ContentXml must be like this:
--'<xml>
--	<MediaContents>
--		<MediaContent>
--			<MediaContentName>First</MediaContentName>
--			<Description>Empty</Description>
--			<MetaCode>CodeFirst</MetaCode>
--			<MediaFiles>
--				<MediaFile>
--					<MediaFileId>1</MediaFileId>
--					<IsPrimary>1</IsPrimary>
--				</MediaFile>
--				<MediaFile>
--					<MediaFileId>2</MediaFileId>
--					<IsPrimary>0</IsPrimary>
--				</MediaFile>
--			</MediaFiles>
--			<MediaTags>
--				<MediaTag>
--					<MediaTagId>1</MediaTagId>
--				</MediaTag>
--				<MediaTag>
--					<MediaTagId>2</MediaTagId>
--				</MediaTag>
--			</MediaTags>
--		</MediaContent>
--		<MediaContent>
--			<MediaContentName>Second</MediaContentName>
--			<Description>Empty</Description>
--			<MetaCode>CodeSecond</MetaCode>
--			<MediaFiles>
--				<MediaFile>
--					<MediaFileId>2</MediaFileId>
--					<IsPrimary>1</IsPrimary>
--				</MediaFile>
--			</MediaFiles>
--			<MediaTags>
--			</MediaTags>
--		</MediaContent>
--	</MediaContents>
--</xml>'
set arithabort on
declare @MediaContentId int
declare @MediaContentName nvarchar(250)
declare @MetaCode nvarchar(150)
declare @Description nvarchar(500)
declare @MediaFileList xml
declare @MediaTagIdList xml
declare media_content_cursor cursor for 
select 
	doc.col.value('(./MediaContentName)[1]', 'nvarchar(250)'),
	doc.col.value('(./Description)[1]', 'nvarchar(500)'),
	doc.col.value('(./MetaCode)[1]', 'nvarchar(150)'),
	doc.col.query('(./MediaFiles/MediaFile/.)'),
	doc.col.query('(./MediaTags/MediaTag/MediaTagId/.)')
from @ContentXml.nodes('/MediaContents/MediaContent') doc(col) 
open media_content_cursor;
fetch next from media_content_cursor 
into @MediaContentName, @Description, @MetaCode, @MediaFileList, @MediaTagIdList
while @@fetch_status = 0
begin
	insert into dbo.MediaContents
	(
	SiteId,
	SiteGroupId,
	MediaId,
	MediaContentName,
	Description,
	MetaCode,
	CreatedBy,
	DateCreated
	)
	values
	(
	@SiteId,
	@SiteGroupId,
	@MediaId,
	@MediaContentName,
	@Description,
	@MetaCode,
	@CreatedBy,
	@DateCreated
	)
	set @MediaContentId = SCOPE_IDENTITY()
	insert into dbo.MediaContentFileLinks
	(
	MediaContentId,
	MediaFileId,
	IsPrimary
	)	
	select 
		@MediaContentId,
		childDoc.col.value('(./MediaFileId)[1]', 'int'),
		childDoc.col.value('(./IsPrimary)[1]', 'bit')
	from @MediaFileList.nodes('/MediaFile') childDoc(col)
	insert into dbo.MediaContentTagLinks
	(
	MediaContentId,
	MediaTagId
	)
	select 
		@MediaContentId,
		childDoc.col.value('(.)[1]', 'int')
	from @MediaTagIdList.nodes('/MediaTagId') childDoc(col)
fetch next from media_content_cursor 
	into @MediaContentName, @Description, @MetaCode, @MediaFileList, @MediaTagIdList
end
close media_content_cursor;
deallocate media_content_cursor;


GO

